"""
    Data access methods for partnergate for use with MySql datastore
"""
from utils import get_time
from flaskext.sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class DataAccess():
    """Data access methods for partner gate."""

    def open(self):
        """Open the connection to the database if necessary"""
        pass

    def get_url(self, partner_id, url):
        """See if the url exists in the database"""
        dbresp = db.engine.execute("select first_occurrence from \
                                    partner_gate_db.partner_gate_lookup \
                                    where partner_id = %s and \
                                    tracking_url = CAST(UNHEX(%s) AS BINARY(16)) \
                                    order by first_occurrence desc LIMIT 1; ", partner_id, url)
        first_seen = dbresp.fetchone()
        if not first_seen:
            return -1
        return first_seen['first_occurrence']

    def add_url(self, partner_id, url):
        """Add the url to the database"""
        db.engine.execute("insert into partner_gate_db.partner_gate_lookup \
                           (partner_id, \
                            tracking_url, \
                            first_occurrence, \
                            lookup_status) \
                            Values (%s,UNHEX(%s),UNIX_TIMESTAMP(),1); ", partner_id, url)
        return get_time()

    def get_total(self, partner_id):
        """Get the total number of unique urls for a given partner_id"""
        resp = db.engine.execute("select count(distinct partner_id, tracking_url) as count from \
                                  partner_gate_db.partner_gate_lookup \
                                  where partner_id=%s; ", partner_id)
        total = resp.fetchone()
        if not total:
            return -1
        return total['count']

    def try_execute(self, sql, key, default):
        """Helper method to fetch a single row if present
           otherwise return the default"""
        query = self.execute(sql)
        resp = query.fetchone()
        if not resp:
            return default
        return resp[key]

    def execute(self, sql):
        """Helper method to execute arbitrary sql"""
        return db.engine.execute(sql)

    def close(self):
        """Close the connection to the database"""
        pass